-- This demo shows how collation conflicts arise and how to deal with them.
-- © Erland Sommarskog 2024.

-- Note: if you server collation is case-insensitive, change the database in
-- the USE statement to a Latin1_General_100_CS_AS database

USE Latin1_General_CI_AS
go
-- Create table with some place names.
CREATE TABLE Places (place nvarchar(20) NOT NULL,
                     data  int        NOT NULL,
                     CONSTRAINT pk_places PRIMARY KEY (place))
go
INSERT Places (place, data)
   VALUES(N'New York', 879), (N'London', 232), (N'Paris', 145), 
         (N'Munich', 812), (N'Buenos Aires', 23)
go
-- Create a temp table with two place names.
CREATE TABLE #temp (place nvarchar(20) NOT NULL PRIMARY KEY)
INSERT #temp(place) VALUES(N'London'), (N'PARIS')
go
-- We want to find the places that are in the temp table, but conflict!
SELECT p.place, p.data 
FROM   Places p
JOIN   #temp t ON t.place = p.place
go
-- Coerce the datbase collation for the temp-table column.
SELECT p.place, p.data 
FROM   Places p
JOIN   #temp t ON t.place COLLATE Latin1_General_CI_AS = p.place 
go
-- Rather than hard-coding the collation, you can use database_default.
SELECT p.place, p.data 
FROM   Places p
JOIN   #temp t ON t.place COLLATE DATABASE_DEFAULT = p.place 
go
-- What if we force the collation on the other column to something else?
-- (If you have a CI collation on server level, change to CI below.)
SELECT p.place, p.data
FROM   Places p
JOIN   #temp t ON t.place = p.place COLLATE French_CS_AS
go
-- Comparing a literal to the columns, both work, column collation takes precedence.
SELECT place, data FROM Places WHERE place = 'paris'
SELECT place FROM #temp WHERE place = 'paris'
go
-- Same for a variable.
DECLARE @place nvarchar(20) = 'paris'
SELECT place, data FROM Places WHERE place = @place
SELECT place FROM #temp WHERE place = @place
go
-- Do the temp table in the proper way to avoid the conflict.
DROP TABLE #temp
CREATE TABLE #temp (place nvarchar(20) COLLATE DATABASE_DEFAULT NOT NULL PRIMARY KEY)
INSERT #temp(place) VALUES(N'London'), (N'PARIS')
go
SELECT p.place, p.data 
FROM   Places p
JOIN   #temp t ON t.place = p.place
go
